import numpy as np
import pandas as pd
from scipy.sparse import csr_matrix
from utils.db import get_mysql_conn, get_redis_conn
import json
from tqdm import tqdm
import time
from datetime import datetime

class ItemCF:
    """基于物品的协同过滤推荐算法"""
    
    def __init__(self, user_course_matrix=None):
        """
        初始化ItemCF类
        
        参数:
            user_course_matrix: 用户-课程评分矩阵，格式为 {user_id: {course_id: rating}}
        """
        self.user_course_matrix = user_course_matrix or {}
        self.item_similarity_matrix = {}  # 课程相似度矩阵
        self.mysql_conn = get_mysql_conn()
        self.redis_conn = get_redis_conn()
        
    def load_data_from_db(self):
        """从数据库加载数据"""
        print("从数据库加载数据...")
        
        try:
            # 确保数据库连接有效
            if not self.mysql_conn or not self._test_connection(self.mysql_conn):
                self.mysql_conn = get_mysql_conn()
                
            if not self.mysql_conn:
                raise Exception("无法建立数据库连接")
            
            # 首先查询一下原始数据以理解问题
            with self.mysql_conn.cursor() as cursor:
                cursor.execute("""
                SELECT 
                    user_id, course_id, event_type, extra_data
                FROM user_behaviors
                WHERE event_type IN ('rate', 'complete', 'purchase', 'favorite', 'view')
                LIMIT 5
                """)
                samples = cursor.fetchall()
                
                print("样本数据:")
                for sample in samples:
                    print(f"  {sample}")
            
            # 直接使用原生SQL并手动处理结果
            with self.mysql_conn.cursor() as cursor:
                cursor.execute("""
                SELECT 
                    user_id, 
                    course_id, 
                    CASE 
                        WHEN event_type = 'rate' THEN 
                            CAST(JSON_EXTRACT(extra_data, '$.rating') AS DECIMAL(3,1))
                        WHEN event_type = 'complete' THEN 5.0
                        WHEN event_type = 'purchase' THEN 4.0
                        WHEN event_type = 'favorite' THEN 3.5
                        WHEN event_type = 'view' THEN 2.0
                        ELSE 0
                    END AS rating
                FROM user_behaviors
                WHERE event_type IN ('rate', 'complete', 'purchase', 'favorite', 'view')
                  AND (event_type != 'rate' OR JSON_EXTRACT(extra_data, '$.rating') IS NOT NULL)
                """)
                rows = cursor.fetchall()
            
            # 转换为用户-课程矩阵
            user_course_data = {}
            for row in rows:
                user_id = int(row['user_id'])
                course_id = int(row['course_id'])
                try:
                    rating = float(row['rating'])
                except (ValueError, TypeError):
                    print(f"警告: 无法将评分转换为浮点数: {row['rating']}, 使用默认值0.0")
                    rating = 0.0
                
                # 跳过无效数据
                if user_id <= 0 or course_id <= 0 or rating <= 0:
                    continue
                
                # 对于每个用户-课程组合，只保留最高评分
                if user_id not in user_course_data:
                    user_course_data[user_id] = {}
                
                if course_id not in user_course_data[user_id] or rating > user_course_data[user_id][course_id]:
                    user_course_data[user_id][course_id] = rating
            
            # 统计
            total_ratings = sum(len(courses) for courses in user_course_data.values())
            
            # 保存结果
            self.user_course_matrix = user_course_data
            
            print(f"从数据库加载了 {total_ratings} 条评分数据，涉及 {len(self.user_course_matrix)} 个用户")
            return len(self.user_course_matrix) > 0
            
        except Exception as e:
            print(f"从数据库加载数据失败: {e}")
            import traceback
            traceback.print_exc()
            return False
    
    def _test_connection(self, conn):
        """测试数据库连接是否有效"""
        try:
            with conn.cursor() as cursor:
                cursor.execute("SELECT 1")
                cursor.fetchone()
            return True
        except Exception:
            return False
    
    def build_sparse_matrix(self):
        """构建稀疏矩阵表示用户-课程评分"""
        # 获取所有用户和课程ID
        user_ids = list(self.user_course_matrix.keys())
        course_ids = set()
        for user_ratings in self.user_course_matrix.values():
            course_ids.update(user_ratings.keys())
        course_ids = list(course_ids)
        
        # 创建用户和课程的ID映射
        self.user_id_map = {user_id: i for i, user_id in enumerate(user_ids)}
        self.course_id_map = {course_id: i for i, course_id in enumerate(course_ids)}
        self.reverse_user_map = {i: user_id for user_id, i in self.user_id_map.items()}
        self.reverse_course_map = {i: course_id for course_id, i in self.course_id_map.items()}
        
        # 构建稀疏矩阵的数据
        row_indices = []
        col_indices = []
        ratings = []
        
        for user_id, user_ratings in self.user_course_matrix.items():
            user_idx = self.user_id_map[user_id]
            for course_id, rating in user_ratings.items():
                course_idx = self.course_id_map[course_id]
                row_indices.append(user_idx)
                col_indices.append(course_idx)
                # 确保评分是浮点数
                try:
                    ratings.append(float(rating))
                except (ValueError, TypeError):
                    # 如果无法转换为浮点数，使用默认值
                    ratings.append(0.0)
        
        # 创建稀疏矩阵
        self.rating_matrix = csr_matrix(
            (ratings, (row_indices, col_indices)), 
            shape=(len(user_ids), len(course_ids))
        )
        
        print(f"构建了稀疏矩阵，大小: {self.rating_matrix.shape}")
        return self.rating_matrix
    
    def calculate_item_similarity(self):
        """计算课程之间的相似度"""
        print("计算课程相似度...")
        
        # 确保已经构建了稀疏矩阵
        if not hasattr(self, 'rating_matrix'):
            self.build_sparse_matrix()
        
        # 获取课程数量
        _, n_items = self.rating_matrix.shape
        
        # 计算每个课程被评分的用户数（用于后续的惩罚因子）
        course_rated_count = np.array(self.rating_matrix.sum(axis=0)).flatten()
        
        # 初始化课程相似度矩阵
        self.item_similarity_matrix = {}
        
        # 计算课程相似度（使用改进的余弦相似度）
        for i in tqdm(range(n_items), desc="计算课程相似度"):
            course_id_i = self.reverse_course_map[i]
            self.item_similarity_matrix[course_id_i] = {}
            
            # 获取课程i的评分向量
            i_ratings = self.rating_matrix.getcol(i).toarray().flatten()
            i_norm = np.sqrt(np.sum(i_ratings ** 2))
            
            # 只计算有评分的课程
            if i_norm == 0:
                continue
            
            # 计算与所有其他课程的相似度
            for j in range(n_items):
                if i == j:
                    continue
                    
                course_id_j = self.reverse_course_map[j]
                
                # 获取课程j的评分向量
                j_ratings = self.rating_matrix.getcol(j).toarray().flatten()
                j_norm = np.sqrt(np.sum(j_ratings ** 2))
                
                # 只计算有评分的课程
                if j_norm == 0:
                    continue
                
                # 计算点积
                dot_product = np.sum(i_ratings * j_ratings)
                
                # 计算余弦相似度
                similarity = dot_product / (i_norm * j_norm)
                
                # 添加惩罚因子，减轻热门课程的影响
                # 惩罚公式：sim(i,j) * log(1 + min_count / max_count)
                min_count = min(course_rated_count[i], course_rated_count[j])
                max_count = max(course_rated_count[i], course_rated_count[j])
                penalty = np.log(1 + min_count / max_count) if max_count > 0 else 1
                
                adjusted_similarity = similarity * penalty
                
                # 只保存相似度大于0的记录
                if adjusted_similarity > 0:
                    self.item_similarity_matrix[course_id_i][course_id_j] = adjusted_similarity
        
        print("课程相似度计算完成")
        return self.item_similarity_matrix
    
    def save_similarity_to_redis(self):
        """将计算出的相似度保存到Redis"""
        print("将课程相似度保存到Redis...")
        
        try:
            if not self.redis_conn:
                self.redis_conn = get_redis_conn()
            
            # 删除旧的相似度数据
            old_keys = self.redis_conn.keys("item_cf:similarity:*")
            if old_keys:
                for key in old_keys:
                    self.redis_conn.delete(key)
            
            # 保存相似度数据
            pipe = self.redis_conn.pipeline()
            for course_id, similarities in self.item_similarity_matrix.items():
                # 确保所有键和值都是基本类型（字符串、数字等）
                serializable_dict = {str(k): float(v) for k, v in similarities.items()}
                key = f"item_cf:similarity:{course_id}"
                pipe.set(key, json.dumps(serializable_dict))
            
            # 添加更新时间
            now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            pipe.set("item_cf:update_time", now)
            
            # 执行所有Redis操作
            pipe.execute()
            
            print(f"成功将 {len(self.item_similarity_matrix)} 个课程的相似度数据保存到Redis")
            return True
            
        except Exception as e:
            print(f"保存课程相似度失败: {e}")
            import traceback
            traceback.print_exc()
            return False
    
    def load_similarity_from_redis(self):
        """从Redis加载课程相似度"""
        print("从Redis加载课程相似度...")
        
        try:
            if not self.redis_conn:
                self.redis_conn = get_redis_conn()
                
            # 获取更新时间
            update_time = self.redis_conn.get("item_cf:update_time")
            if update_time:
                # 如果是字节类型，解码为字符串
                if isinstance(update_time, bytes):
                    update_time = update_time.decode('utf-8')
                print(f"相似度数据的更新时间: {update_time}")
            
            # 获取所有课程相似度键
            similarity_keys = self.redis_conn.keys("item_cf:similarity:*")
            
            if not similarity_keys:
                print("Redis中没有找到课程相似度数据")
                return False
            
            # 清空当前相似度矩阵
            self.item_similarity_matrix = {}
            
            # 加载相似度数据
            for key in similarity_keys:
                course_id = None  # 初始化course_id变量
                try:
                    # 解析键名以获取课程ID
                    key_str = key
                    if isinstance(key, bytes):
                        key_str = key.decode('utf-8')
                    
                    # 从键名中提取课程ID
                    key_parts = key_str.split(":")
                    if len(key_parts) >= 3:
                        course_id = int(key_parts[-1])
                    else:
                        print(f"无效的键名格式: {key_str}")
                        continue
                    
                    # 获取相似度数据
                    similarity_json = self.redis_conn.get(key)
                    
                    if similarity_json:
                        # 如果是字节类型，解码为字符串
                        if isinstance(similarity_json, bytes):
                            similarity_json = similarity_json.decode('utf-8')
                        
                        # 解析为字典
                        similarity_dict = json.loads(similarity_json)
                        
                        # 确保所有键都是字符串，值都是浮点数
                        self.item_similarity_matrix[course_id] = {int(k): float(v) for k, v in similarity_dict.items()}
                except Exception as e:
                    if course_id is not None:
                        print(f"处理课程ID {course_id} 的相似度数据时出错: {e}")
                    else:
                        print(f"处理键 {key} 时出错: {e}")
                    import traceback
                    traceback.print_exc()
                    continue
            
            print(f"从Redis加载了 {len(self.item_similarity_matrix)} 个课程的相似度数据")
            return True
            
        except Exception as e:
            print(f"从Redis加载课程相似度失败: {e}")
            import traceback
            traceback.print_exc()
            return False
    
    def recommend(self, user_id, n=10, exclude_courses=None):
        """
        为指定用户推荐课程
        
        参数:
            user_id: 用户ID
            n: 推荐课程数量
            exclude_courses: 需要排除的课程ID列表（如已选课程）
            
        返回:
            推荐课程列表，格式为 [(course_id, predicted_rating), ...]
        """
        # 如果不存在该用户的评分记录，返回空列表
        if user_id not in self.user_course_matrix:
            print(f"用户 {user_id} 没有评分记录")
            return []
        
        # 获取用户已评分的课程
        user_rated_courses = self.user_course_matrix[user_id]
        
        # 初始化排除列表
        if exclude_courses is None:
            exclude_courses = []
        
        # 将用户已评分的课程也加入到排除列表中
        exclude_courses.extend(user_rated_courses.keys())
        
        # 计算预测评分
        candidate_courses = {}
        
        for course_id, rating in user_rated_courses.items():
            # 获取与该课程相似的课程
            if course_id not in self.item_similarity_matrix:
                continue
                
            similar_courses = self.item_similarity_matrix[course_id]
            
            # 计算预测评分
            for similar_course_id, similarity in similar_courses.items():
                # 跳过需要排除的课程
                if int(similar_course_id) in exclude_courses:
                    continue
                
                # 累加预测评分的分子和分母
                if similar_course_id not in candidate_courses:
                    candidate_courses[similar_course_id] = {'numerator': 0, 'denominator': 0}
                
                candidate_courses[similar_course_id]['numerator'] += similarity * rating
                candidate_courses[similar_course_id]['denominator'] += similarity
        
        # 计算最终预测评分并排序
        predictions = []
        for course_id, values in candidate_courses.items():
            if values['denominator'] > 0:
                predicted_rating = values['numerator'] / values['denominator']
                predictions.append((int(course_id), predicted_rating))
        
        # 按预测评分降序排序并返回前N个
        recommendations = sorted(predictions, key=lambda x: x[1], reverse=True)[:n]
        
        return recommendations
    
    def train_model(self):
        """训练模型，计算课程相似度并保存到Redis"""
        # 重新获取数据库连接
        from utils.db import get_mysql_conn, get_redis_conn
        self.mysql_conn = get_mysql_conn(force_new=True)
        self.redis_conn = get_redis_conn(force_new=True)
        
        # 从数据库加载数据
        if not self.load_data_from_db():
            print("加载数据失败，无法训练模型")
            return False
        
        # 构建稀疏矩阵
        self.build_sparse_matrix()
        
        # 计算课程相似度
        self.calculate_item_similarity()
        
        # 保存相似度到Redis
        return self.save_similarity_to_redis()

# 测试代码
if __name__ == "__main__":
    # 创建ItemCF实例
    item_cf = ItemCF()
    
    # 尝试从Redis加载相似度数据
    if not item_cf.load_similarity_from_redis():
        print("Redis中没有相似度数据，开始训练模型...")
        item_cf.train_model()
    
    # 测试推荐功能
    user_id = 1  # 替换为实际用户ID
    recommendations = item_cf.recommend(user_id, n=5)
    
    print(f"\n为用户 {user_id} 的推荐课程:")
    for course_id, score in recommendations:
        print(f"课程ID: {course_id}, 预测评分: {score:.2f}") 